
/*
This program makes Appendix Table A.2


*/



*1. Make Appendix Table A.2 (Aggregate Stats for Universe of Firms)
********************************************************************
use $input/gvc_output.dta, clear

 replace mne_status="MNE" if mne_status=="MNEFORMAN"
 replace mne_status="DOM" if mne_status=="DOMIMP"
 
 collapse (sum) firms prof_firms emp emp_man emp_prof emp_manage ///
				sales importer rp_importer imp_value rp_imp_value ///
				exporter rp_exporter exp_value rp_exp_value aff_sales ///
				man_aff_sales , by(mne_status)
				
*make share variables
foreach var in emp sales imp_value exp_value {
   egen `var'_tot=sum(`var')
   gen double `var'_share=round(`var'/`var'_tot, .01)
   }

*scale variables
replace emp=emp/1000	
replace sales=sales/1000000
gen imports=imp_value/1000000
gen exports=exp_value/1000000
	
browse mne_status firms emp emp_share sales sales_share imports imp_value_share exports exp_value_share	

gen mne_st="Domestic" if mne_status=="DOM"
  replace mne_st="Foreign" if mne_status=="FOR"
  replace mne_st="US MNEs" if mne_status=="MNE"
  label variable mne_st "Firm Type"
  
  label variable firms "Firms"
  label variable emp "Employment"
  label variable emp_share "Emp Share"
  label variable sales "Sales (\$T)"
  label variable sales_share "Sales Share"
  label variable imports "Imports (\$B)"
  label variable exports "Exports (\$B)"
  label variable imp_value_share "Import Share"
  label variable exp_value_share "Export Share"
  
  format firms emp sales imports  exports %9.0fc
  format emp_share sales_share imp_value_share exp_value_share %9.2f
  
export excel mne_st firms emp emp_share sales sales_share imports imp_value_share exports exp_value_share	 ///
  using $app_tables, sheetreplace sheet(TABLE_A2) firstrow(varlabels)

 foreach vv in importer exporter {
   gen `vv'_share=`vv'/firms
   gen rp_`vv'_share=rp_`vv'/firms
   }
   
   
   
/*   

*CODE to calculate trader and related-party trader stats for aggregate sample

  format  importer_share exporter_share rp_importer_share rp_exporter_share %9.2fc
  
  browse mne_st firms importer rp_importer exporter rp_exporter	 ///
	importer_share exporter_share rp_importer_share rp_exporter_share 
	
export excel mne_st firms importer rp_importer exporter rp_exporter	 ///
	importer_share exporter_share rp_importer_share rp_exporter_share ///
  using $app_tables, sheetreplace sheet(aggregate_trade_status) firstrow(varlabels)
*/

  
******************************************************************** 

 
  
  
*2. Make Appendix Table A.3  (Aggregate Stats for Manufacturing Firm Sample)
********************************************************************				
use $input/gvc_output.dta, clear

 replace mne_status="DOM" if mne_status=="DOMIMP"


 collapse (sum) firms prof_firms emp emp_man emp_prof emp_manage ///
				sales sales_man importer rp_importer imp_value rp_imp_value ///
				exporter rp_exporter exp_value rp_exp_value aff_sales ///
				man_aff_sales , by(mne_status type)
********************************************************************
				
*make share variables
foreach var in emp emp_man sales sales_man imp_value exp_value {
   egen `var'_tot=sum(`var')
   gen double `var'_share=round(`var'/`var'_tot, .01)
   }


*scale variables
replace emp=emp/1000	
replace emp_man=emp_man/1000
replace sales=sales/1000000
replace sales_man=sales_man/1000000
gen imports=imp_value/1000000
gen exports=exp_value/1000000

sort type mne_status
	
browse type mne_status firms emp emp_share emp_man emp_man_share sales sales_share sales_man sales_man_share imports imp_value_share exports exp_value_share	

gen mne_st="Domestic" if mne_status=="DOM"
  replace mne_st="Importers" if mne_status=="DOMIMP"
  replace mne_st="Foreign MNEs" if mne_status=="FOR"
  replace mne_st="No foreign manuf affiliates" if mne_status=="MNE"
  replace mne_st="With foreign manuf affiliates" if mne_status=="MNEFORMAN"
  label variable mne_st "Firm Type"
  
  
  /* 
  *Code for the Levels
  
  label variable firms "Firms"
  label variable emp "Employment"
  label variable emp_share "Emp Share"
  label variable emp_man_share "Manuf Emp Share"
  label variable sales "Sales (\$B)"
  label variable sales_share "Sales Share"
  label variable sales_share "Sales Share"
  label variable imports "Imports (\$B)"
  label variable exports "Exports (\$B)"
  label variable imp_value_share "Import Share"
  label variable exp_value_share "Export Share"
  sort type mne_status 
export excel type mne_st firms emp emp_share sales sales_share imports imp_value_share exports exp_value_share	 ///
  using $app_tables, sheetreplace sheet(TABLE_A.3_levels) firstrow(varlabels)
  */
  
  label variable firms "Firms"
  label variable emp_share "Emp"
  label variable emp_man_share "Man Emp"
  label variable sales_share "Sales"
  label variable sales_man_share "Man Sales"
  label variable imp_value_share "Imports"
  label variable exp_value_share "Exports"
  format firms emp sales imports  exports %9.0gc
  format emp_share emp_man_share sales_share sales_man_share imp_value_share exp_value_share %9.2f

  
*Appendix Table A.3 
export excel mne_st firms emp_share emp_man_share sales_share sales_man_share imp_value_share exp_value_share	if type=="MAN" ///
  using $app_tables, sheetreplace sheet(TABLE_A3) firstrow(varlabels)	
  
  //  NOTE: to obtain the total for each column as displayed in the paper, simply sum each column in the Excel file //
  
********************************************************************  
  
  
*3.  Make Appendix Tables A.4 and A.5
********************************************************************  

*Trade flows and traders
foreach vv in imp exp {
	gen `vv'orter_sh=`vv'orter/firms
	gen `vv'ort_sh=`vv'orts/sales
	gen rp_`vv'orter_sh=rp_`vv'orter/`vv'orter
	gen rp_`vv'ort_sh=rp_`vv'_value/`vv'_value
	}
	

format importer_sh-rp_export_sh %9.2f
browse type mne_st importer_sh-rp_export_sh

browse type mne_st importer_sh exporter_sh import_sh export_sh

browse type mne_st rp_importer_sh rp_exporter_sh rp_import_sh rp_export_sh

format importer_sh exporter_sh import_sh export_sh rp_importer_sh rp_exporter_sh rp_import_sh rp_export_sh %9.2fc

*TABLE A.4 
export excel type mne_st importer_sh exporter_sh import_sh export_sh rp_importer_sh rp_exporter_sh rp_import_sh rp_export_sh if type=="MAN" ///
  using $app_tables, sheetreplace sheet(TABLE_A4) firstrow(varlabels) 
  
*TABLE A.5 
export excel type mne_st importer_sh exporter_sh import_sh export_sh rp_importer_sh rp_exporter_sh rp_import_sh rp_export_sh if type=="NON MAN" ///
  using $app_tables, sheetreplace sheet(TABLE_A5) firstrow(varlabels) 
********************************************************************  
  
  
  
  